<?php
namespace Sfsn\Core\Dao;

use PDO;
use PDOException;
use Sfsn\Core\Exception\Exception;


/**
 * @author shooke
 *         数据库驱动类
 *         底层的数据库链接 和sql执行
 */
class MysqlPdo
{

    public $link = null;
    // 数据库链接
    public $sql = "";

    private $affectedRows = 0;
    /**
     * 数据库配置
     * @var unknown
     */
    private $_config = [];

    private $_PDOStatement = null;
    // pdo返回数据集
    
    /**
     * 构造函数建立连接
     * $exception true时抛出异常 false不抛出
     * @param unknown $config
     * @param string $exception
     */
    public function __construct($config = [], $exception = true)
    {        
        $this->_config = $config;
        try {
            $dns = "mysql:host={$config['DB_HOST']};port={$config['DB_PORT']};dbname={$config['DB_NAME']}";
            $this->link = new PDO($dns, $config['DB_USER'], $config['DB_PWD']);
            $this->link->setAttribute(PDO::ATTR_PERSISTENT, $config['DB_PCONNECT']); // 设置数据库连接为持久连接
                                                                                     // $this->link->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);//关闭自动提交事务
            $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 设置抛出错误
            $this->link->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL); // 指定数据库返回的NULL值在php中对应的数值 不变
            $this->link->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL); // 强制PDO 获取的表字段字符的大小写转换,原样使用列值
            $this->link->exec("SET NAMES {$config['DB_CHARSET']}"); // 设置编码
        } catch (PDOException $e) {
            $this->link = false;
            $exception && $this->_exception($e);
        }
    }
    /**
     * 返回连接状态
     * @return Ambigous <NULL, boolean, \PDO>
     */
    public function success()
    {
        return $this->link === false ? false : true;
    }

    /**
     * 事务开始
     *
     * @see \Sfsn\Core\Db\DbAbstract::begin()
     */
    public function beginTransaction()
    {
        $this->link->beginTransaction();
    }

    /**
     * 事务提交
     *
     * @see \Sfsn\Core\Db\DbAbstract::commit()
     */
    public function commit()
    {
        $this->link->commit();
    }

    /**
     * 事务回滚
     *
     * @see \Sfsn\Core\Db\DbAbstract::rollBack()
     */
    public function rollBack()
    {
        $this->link->rollBack();
    }

    /**
     * 执行sql查询
     *
     * @see \Sfsn\Core\Db\DbAbstract::query()
     */
    public function query($sql, $params = array())
    {
        foreach ($params as $k => $v) {
            $sql = str_replace(':' . $k, $this->escape($v), $sql);
        }
        $this->sql = $sql;
        try {
            $this->free(); // 释放先前的资源
            $this->_PDOStatement = $this->link->prepare($this->sql);
            $res = $this->_PDOStatement->execute();
            return $res;
        } catch (PDOException $e) {
            $this->_exception($e);
        }
    }

    /**
     * 执行sql命令
     *
     * @see \Sfsn\Core\Db\DbAbstract::execute()
     */
    public function execute($sql, $params = array())
    {
        foreach ($params as $k => $v) {
            $sql = str_replace(':' . $k, $this->escape($v), $sql);
        }
        $this->sql = $sql;
        try {
            $this->free(); // 释放先前的资源
            $this->_PDOStatement = $this->link->prepare($this->sql);
            $this->_PDOStatement->execute();
            $this->affectedRows = $this->_PDOStatement->rowCount();
            return $this->affectedRows;
        } catch (PDOException $e) {
            $this->_exception($e);
        }
    }

    /**
     * 从结果集中取得一行作为关联数组，或数字数组，或二者兼有
     *
     * @see \Sfsn\Core\Db\DbAbstract::fetch()
     */
    public function fetch($result_type = PDO::FETCH_ASSOC)
    {
        return $this->_PDOStatement->fetch($result_type);
    }

    /**
     * 从结果集中取得所有行作为关联数组，或数字数组，或二者兼有
     *
     * @see \Sfsn\Core\Db\DbAbstract::fetchAll()
     */
    public function fetchAll($result_type = PDO::FETCH_ASSOC)
    {
        return $this->_PDOStatement->fetchAll($result_type);
    }

    /**
     * 取得前一次 MySQL 操作所影响的记录行数
     * 
     * @see \Sfsn\Core\Db\DbAbstract::affectedRows()
     */
    public function affectedRows()
    {
        return $this->affectedRows;
    }
    /**
     * 获取上一次插入的id
     * @see \Sfsn\Core\Db\DbAbstract::lastId()
     */
    public function lastId()
    {
        return $this->link->lastInsertId();
    }
    /**
     * 获取SQL语句
     * @see \Sfsn\Core\Db\DbAbstract::getSql()
     */
    public function getSql()
    {
        return $this->sql;
    }
    /**
     * 获取数据库表
     * @see \Sfsn\Core\Db\DbAbstract::getTables()
     */
    public function getTables($database)
    {
        $this->sql = "SHOW TABLES FROM `{$database}`";
        $this->query($this->sql);
        $tables = $this->fetchAll();
        $data = [];
        foreach ($tables as $tab){
            $data[] = $tab['Tables_in_' . $database];
        }       
        return $data;
    }
    /**
     * 获取表结构
     * @see \Sfsn\Core\Db\DbAbstract::getFields()
     */
    public function getFields($table)
    {
        $this->sql = "SHOW FULL FIELDS FROM {$table}";
        $this->query($this->sql);
        $fields = $this->fetchAll();
        $data = [];
        //格式化处理
        foreach ($fields as $val){            
            $data[$val['Field']] = $val;
        }
        return $data;
    }
    
    /**
     * 获取行数
     * @see \Sfsn\Core\Db\DbAbstract::count()
     */
    public function count($table, $where, $field = '*')
    {
        $this->sql = "SELECT count($field) FROM $table $where";
        $this->query($this->sql);
        $count = $this->_PDOStatement->fetchColumn();
        return intval($count);
    }
    /**
     * 取得数据库版本
     * @see \Sfsn\Core\Db\DbAbstract::version()
     */
    public function version()
    {
        return $this->link->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
    }
    /**
     * 数据过滤
     * @param unknown $value
     * @return array|string
     */
    public function escape($value)
    {
        if (is_array($value)) {
            return array_map(array(
                $this,
                'escape'
            ), $value);
        } else {
            return addslashes($value);
        }
    }
    /**
     * 表单点号(引号处理)
     *
     * @param unknown $value
     * @return array|string
     */
    private function _fieldEscape($value)
    {
        if (is_array($value)) {
            return array_map([
                $this,
                '_fieldEscape'
            ], $value);
        } else {
            if(preg_match('/.*\(.*\)/', $value)){
                return $value;
            }else{
                return '`'. $value .'`';
            }    
        }
    }
    public function pareField($field)
    {        
        $field = $this->_fieldEscape($field);
        $output = [];
        foreach ($field as $key=>$value){
            if (is_numeric($key)) {
                $output[] = $this->escape($value);
            } else {
                $key = $this->escape($key);
                $value = $this->escape($value);
                $output[] = $value . ' as ' . $key;
            }
        }
        return implode(',', $output);        
    }
    // 解析待添加或修改的数据
    public function parseAdd($data)
    {
        // 如果数据是字符串，直接返回
        if (is_string($data)) {
            return $this->escape($data);
        }
        
        if (is_array($data) && ! empty($data)) {            
            $fields = array(); // 插入字段
            $Values = array(); // 插入数据
            $tempData = array(); // 临时存放防注入处理后的数据
                                 // 如果第一个元素是数组则进行二维数组多条插入处理
            if (is_array($data[0])) {
                $fields = $this->escape(array_keys($data[0])); // 字段处理
                $tempData = $this->escape(array_values($data)); // 防注入处理
                                                                           // 拆分为插入语句
                foreach ($tempData as $val) {
                    $Values[] = "('" . implode("','", $val) . "')";
                }
                // 最终插入语句
                return " (`" . implode("`,`", $fields) . "`) VALUES " . implode(",", $Values);
            } else {
                // 一位数组单条插入处理
                $fields = $this->escape(array_keys($data));
                $Values = $this->escape(array_values($data));
                return " (`" . implode("`,`", $fields) . "`) VALUES ('" . implode("','", $Values) . "') ";
            }               
                
        }
        return false;
    }
    public function parseUpdate($data)
    {
        // 如果数据是字符串，直接返回
        if (is_string($data)) {
            return $this->escape($data);
        }
        
        if (is_array($data) && ! empty($data)) {            
            $output = array();
            foreach ($data as $key => $value) {
                $key = $this->escape($key);
                $value = $this->escape($value);
                $output[] = " `{$key}` = '{$value}'";
            }
            return implode(',', $output);               
        }
        return false;
    }
    
    // 解析查询条件
    public function parseCondition($options)
    {
        $condition = '';
        if (! empty($options['where']) && is_array($options['where'])) {            
            $condition = " WHERE ";
            foreach ($options['where'] as $key => $value) {
                
                if(is_array($value)){
                    /**
                     * 当用户输入的是多参数时会产生where是多维数组
                     * User::where('id','>','1')->find();
                     * User::where('id','1')->find();
                     */
                    $num = count($value);
                    switch ($num) {
                        case 3:                            
                            $value = $this->escape($value);
                            $condition .= "`{$value[0]}` {$value[1]} '{$value[2]}'" . " AND ";               
                            break;
                        case 2:
                            $value = $this->escape($value);
                            $condition .= "`{$value[0]}` = '{$value[2]}'" . " AND ";
                    }
                }else{
                    /**
                     * 当用户传入的参数是数组或字符串
                     * User::where(['id'=>'1','phone'=>'123'])->find();
                     * User::where("'id'=1 and phone=123")->find();
                     */
                    if (is_numeric($key)){
                        $condition .=  $value . " AND ";
                    }else {
                        $field = $this->escape($key);
                        $val = $this->escape($value);
                        $condition .= " `{$field}` = '{$val}' AND ";
                    }                    
                }
                                
            }
            $condition = substr($condition, 0, - 4);            
        }
        
        if (! empty($options['group']) && is_string($options['group'])) {
            $condition .= " GROUP BY " . $options['group'];
        }
        if (! empty($options['having']) && is_string($options['having'])) {
            $condition .= " HAVING " . $options['having'];
        }
        if (! empty($options['order']) && is_string($options['order'])) {
            $condition .= " ORDER BY " . $options['order'];
        }
        if (! empty($options['limit']) && (is_string($options['limit']) || is_numeric($options['limit']))) {
            $condition .= " LIMIT " . $options['limit'];
        }
        
        return $condition;
    }

    /**
     * 异常处理
     *
     * @param PDOException $e            
     * @throws Exception
     */
    private function _exception(PDOException $e)
    {
        $error_sql = $this->sql;
        $error = $e->errorInfo[2];
        $errorno = $e->errorInfo[1];
        $message = $e->getMessage();
        //$trace = $e->getTraceAsString();
        
        $str = " {$message}<br>
	    <b>SQL</b>: {$error_sql}<br>
	    <b>错误详情</b>: {$error}<br>
	    <b>错误代码</b>:{$errorno}<br>";
//         if (DEBUG) {
//             $str .= "<b>异常追踪</b>:{$trace}<br>";
//         }
        throw new Exception($str);
    }
    
    /**
     * 释放资源
     * @see \Sfsn\Core\Db\DbAbstract::free()
     */
    public function free()
    {
        $this->_PDOStatement = null;
    }
    
    /**
     * 断开链接
     * @see \Sfsn\Core\Db\DbAbstract::close()
     */
    public function close()
    {
        $this->link = null;
    }
    /**
     * 关闭数据库
     */
    public function __destruct()
    {
        $this->close();
    }
}
